package com.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.entity.Comment;
import com.entity.Contact;
import com.entity.Document;
import com.entity.Idea;
import com.entity.Trend;
import com.entity.User;
import com.util.DBHelp;
import com.util.RowMapper;

public class CommentDao {
	private DBHelp<Comment> db=new DBHelp<Comment>();
	
	private class CommentRowMapper implements RowMapper<Comment>{

		public Comment mapRow(ResultSet rs) throws SQLException {
			Comment c=new Comment();
			c.setId(rs.getString("id"));
			c.setCreateTime(rs.getString("createtime"));
			c.setMessage(rs.getString("message"));
			
			
			User u=new User();
			c.setUserid(rs.getString("userid"));
			u.setId(rs.getString("userid"));
			u.setImage(rs.getString("image"));
			u.setUserName(rs.getString("username"));
			c.setUser(u);
			
			Idea i=new Idea();
			i.setId(rs.getString("ideaid"));
			c.setIdeaid(rs.getString("ideaid"));
			c.setIdea(i);
			
			Trend t=new Trend();
			c.setTrendid(rs.getString("trendid"));
			t.setId(rs.getString("trendid"));
			c.setTrend(t);
			
			Contact ct=new Contact();
			ct.setId(rs.getString("contactid"));
			c.setContactid(rs.getString("contactid"));
			c.setContact(ct);
			
			Document d=new Document();
			c.setDocumentid(rs.getString("documentid"));
			d.setId(rs.getString("documentid"));
			c.setDocument(d);
			return c;
		}
	}
	
	public void save(Comment c) {
		String sql="insert into t_comment(id,message,createtime,userid,trendid,documentid,ideaid,contactid) values (?,?,?,?,?,?,?,?) ";
		db.executeSQL(sql, c.getId(),c.getMessage(),c.getCreateTime(),c.getUserid(),c.getTrendid(),c.getDocumentid(),c.getIdeaid(),c.getContactid());	
	}
	
	public List<Comment> findByTrendid(String trendid) {
		// TODO Auto-generated method stub
		String sql="select td.id,td.message,td.createtime,td.userid,tu.username,tu.image from t_comment AS td left join t_user AS tu on tu.id=td.userid where trendid=?";
		return db.executeQueryForList(sql, new CommentRowMapper() , trendid);
	}
	
	public List<Comment> findAll(){
		StringBuilder sql=new StringBuilder();
		sql.append("select tt.id,tt.message,tt.createtime,tt.userid,tt.trendid,tt.ideaid,tt.documentid,tt.contactid,tu.username,tu.image ");
		sql.append("from t_comment AS tt ");
		sql.append("left join t_user AS tu on tu.id=tt.userid ");
		sql.append("order by tt.createtime desc");
		return db.executeQueryForList(sql.toString(), new CommentRowMapper());
	}
	
	public void saveFormContact(Comment co){
		String sql="insert into t_comment(id,message,createtime,contactid,userid) values(?,?,?,?,?)";
		db.executeSQL(sql,new CommentRowMapper(),co.getId(),co.getMessage(),co.getCreateTime(),co.getContactid(),co.getUserid());
	}
	
	public void saveSayFromDoc(Comment c){
		String sql="insert into t_comment(id,createtime,message,userid,trendid,documentid,contactid,ideaid) values(?,?,?,?,?,?,?,?)";
		db.executeSQL(sql, c.getId(),c.getCreateTime(),c.getMessage(),c.getUserid(),c.getTrendid(),c.getDocumentid(),c.getContactid(),c.getIdeaid());
	}



	public boolean saveMsgFromIdeaToComment(Comment c) {
		String sql="insert into t_comment(id,message,userid,ideaid,createtime) values(?,?,?,?,?)";
		return db.executeSQL(sql, c.getId(),c.getMessage(),c.getUserid(),c.getIdeaid(),c.getCreateTime());
	}

	
	public List<Comment> findAllCommenttoContact() {
		String sql="SELECT tc.id,tc.message,tc.createtime,tc.userid,tc.trendid,tc.contactid,tc.documentid,tc.ideaid,tu.username,tu.image FROM t_comment AS tc LEFT JOIN t_user AS tu ON tu.id=tc.userid WHERE contactid IS NOT NULL";
		return db.executeQueryForList(sql, new CommentRowMapper());
	}


	public List<Comment> findAllCommenttoIdea() {
		String sql="SELECT tc.id,tc.message,tc.createtime,tc.userid,tc.trendid,tc.contactid,tc.documentid,tc.ideaid,tu.username,tu.image FROM t_comment AS tc LEFT JOIN t_user AS tu ON tu.id=tc.userid WHERE ideaid IS NOT NULL";
		return db.executeQueryForList(sql, new CommentRowMapper());
	}

	public boolean delById(String id) {
		String sql="delete from t_comment where id=?";
		return db.executeSQL(sql, id);
	}
	public boolean deByIdeaid(String id){
		String sql="delete from t_comment where ideaid=?";
		return db.executeSQL(sql, id);
	}
	


	public List<Comment> findByDocumentId(String id) {
		// TODO Auto-generated method stub
		String sql="select td.id,td.message,td.createtime,td.trendid,td.documentid,td.ideaid,td.contactid,td.userid,tu.username,tu.image from t_comment AS td left join t_user AS tu on tu.id=td.userid where documentid= ? order by td.createtime desc";
		return db.executeQueryForList(sql, new CommentRowMapper(), id);
	}

	public void delByCommentId(String id) {
		// TODO Auto-generated method stub
		String sql="delete from t_comment where id=? ";
		db.executeSQL(sql,new CommentRowMapper(),id);
	}

	public void delByDocumentId(String id) {
		// TODO Auto-generated method stub
		String sql="delete from t_comment where documentid=? ";
		db.executeSQL(sql,new CommentRowMapper(),id);
	}

}
